using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Academia.BI
{
    public partial class SqlDataManager
    {
        #region Constants
        private const string CONNECTION_STRING = "AcademiaConnectionString";
        #endregion
        #region Methods
        #region GetReader

        public static SqlDataReader GetReader(string command)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString);
                return GetReader(command, connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static SqlDataReader GetReader(string command, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString);
                return GetReader(command, connection, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static SqlDataReader GetReader(string command, string connectionString)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                return GetReader(command, connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static SqlDataReader GetReader(string command, string connectionString, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                return GetReader(command, connection, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public static SqlDataReader GetReader(string command, SqlConnection connection)
        {
            try
            {
                return GetReader(command, connection, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static SqlDataReader GetReader(string command, SqlConnection connection, SqlParameter[] parameters)
        {
            try
            {
                System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(command, connection);
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                if (parameters!=null)
                {
                    foreach (SqlParameter parameter in parameters)
                        sqlCommand.Parameters.Add(parameter);
                }
                return sqlCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }        
        
        #endregion
        #region ExecuteNonQuery
        public static void ExecuteNonQuery(string command)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString);
                ExecuteNonQuery(command, connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void ExecuteNonQuery(string command, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString);
                ExecuteNonQuery(command, connection,parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }        
        public static void ExecuteNonQuery(string command, string connectionString)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                ExecuteNonQuery(command,connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void ExecuteNonQuery(string command, string connectionString, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                ExecuteNonQuery(command, connection, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }        
        
        public static void ExecuteNonQuery(string command,SqlConnection connection)
        {
            try
            {
                ExecuteNonQuery(command, connection, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public static void ExecuteNonQuery(string command, SqlConnection connection,SqlParameter[] parameters)
        {
            try
            {
                bool connectionOpen = false;
                System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(command, connection);
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                    connectionOpen = true;
                }
                if (parameters!=null)
                {
                    foreach(SqlParameter parameter in parameters)
                        sqlCommand.Parameters.Add(parameter);
                }
                sqlCommand.ExecuteNonQuery();
                if (connectionOpen)
                    connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region ExecuteScalar
        public static object ExecuteScalar (string command)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString);
                return ExecuteScalar(command, connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static object ExecuteScalar (string command, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString);
                return ExecuteScalar(command, connection, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static object ExecuteScalar (string command, string connectionString)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                return  ExecuteScalar(command, connection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static object ExecuteScalar (string command, string connectionString, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                return  ExecuteScalar(command, connection, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static object ExecuteScalar (string command, SqlConnection connection)
        {
            try
            {
                return ExecuteScalar(command, connection, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static object ExecuteScalar(string command, SqlConnection connection, SqlParameter[] parameters)
        {
            try
            {
                bool connectionOpen = false;
                System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(command, connection);
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                    connectionOpen = true;
                }
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                        sqlCommand.Parameters.Add(parameter);
                }
                return sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #endregion

    }
}